<?php
declare(strict_types=1);

namespace App\Controller\Admin\V1\PointPosition;

use App\Controller\Admin\AdminController;
use App\Helper\Utils;
use App\Job\PositionStaticJob;
use App\Model\ExportFile;
use App\Model\PointPosition;
use App\Model\PositionPaper;
use App\Model\PositionSupervise;
use App\Model\PositionSuperviseSheet;
use App\Repositories\AreaRepository;
use App\Repositories\PointPositionCategoryRepository;
use App\Repositories\PointPositionRepository;
use App\Repositories\PositionPaperRepository;
use App\Repositories\UserRepository;
use App\Services\QueueService;
use Hyperf\DbConnection\Db;
use Hyperf\Di\Annotation\Inject;

class StaticsController extends AdminController{

    /**
     * @Inject
     * @var QueueService
     */

    public $queue_service;

    public function area(){
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $areas = AreaRepository::getInstance()->all(['id','name'],[[
            'field'=>'parent_id',
            'value' => 223
        ]],[[
            'field'=>'listorder',
            'sort'=>'asc'
        ]]);
        $query = PositionSupervise::query()->select(
            DB::raw('count(position_id) as count_nums'),
            DB::raw('sum(full_score) as all_full_score'),
            DB::raw('sum(final_score) as all_final_score'),
            DB::raw('sum(final_score)/sum(full_score) as avg_score'),
            'district_id'
        )->join('point_position', function ($join) {
            $join->on('position_supervise.position_id', '=', 'point_position.id')
                ->whereNull('point_position.deleted_at');;
        });
        if($start_time&&$end_time){
            $query->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
        }
        $result_all_nums =$query->groupBy('district_id')->orderBy('avg_score','desc')->get()->keyBy('district_id');
        foreach ($areas as &$item){
            $score_ratio = isset($result_all_nums[$item->id])&&$result_all_nums[$item->id]['avg_score']!=null?$result_all_nums[$item->id]['avg_score']:0;
            $item->score_ratio = sprintf('%.2f',$score_ratio*100).'%';
            $item->avg_score = $score_ratio;
        }
        $areas = collect($areas)->toArray();
        usort($areas,function($a,$b){
            if($a['avg_score']==$b['avg_score']) return 0;
            return $a['avg_score']>$b['avg_score']?-1:1;
        });
        return $this->response->success($areas);
    }

    public function areabak(){
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $areas = AreaRepository::getInstance()->all(['id','name'],[[
            'field'=>'parent_id',
            'value' => 223
        ]],[[
            'field'=>'listorder',
            'sort'=>'asc'
        ]]);
        $query = PositionSupervise::query()->select(DB::raw('count(position_id) as count_nums'),'district_id')->join('point_position', function ($join) {
            $join->on('position_supervise.position_id', '=', 'point_position.id');
        });
        $query_wait_reply = clone $query;
        $query_wait_reply->where('position_supervise.status',PositionSupervise::STATUS_WAITING);
        $query_done_reply = clone $query;
        $query_done_reply->where('position_supervise.status','>=',PositionSupervise::STATUS_REPLY);
        if($start_time&&$end_time){
            $query->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
            $query_wait_reply->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
            $query_done_reply->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
        }
        $result_all_nums =$query->groupBy('district_id')->get()->keyBy('district_id');
        $all_nums=collect($result_all_nums)->sum('count_nums');
        $result_wait_reply_nums = $query_wait_reply->groupBy('district_id')->get()->keyBy('district_id')->toArray();
        $result_done_reply_nums = $query_done_reply->groupBy('district_id')->get()->keyBy('district_id')->toArray();
        foreach ($areas as &$item){
            $item->wait_reply_nums = isset($result_wait_reply_nums[$item->id])?$result_wait_reply_nums[$item->id]['count_nums']:0;
            $item->done_reply_nums = isset($result_done_reply_nums[$item->id])?$result_done_reply_nums[$item->id]['count_nums']:0;
            $item->all_nums = isset($result_all_nums[$item->id])?$result_all_nums[$item->id]['count_nums']:0;
            $base_nums = $item->wait_reply_nums+$item->done_reply_nums;
            $reply_ratio =$base_nums?sprintf('%.2f',$item->done_reply_nums/$base_nums)*100:0;
            $item->reply_ratio = $reply_ratio.'%';
            $count_ratio = $all_nums?sprintf('%.2f',$item->all_nums/$all_nums)*100:0;
            $item->count_ratio = $count_ratio.'%';

        }
        return $this->response->success($areas);
    }

    public function categoryArea(){
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $categories = PointPositionCategoryRepository::getInstance()->all(['id','name','full_score'],[],[[
            'field'=>'id',
            'sort'=>'asc'
        ]]);
        $areas = AreaRepository::getInstance()->all(['id','name'],[[
            'field'=>'parent_id',
            'value' => 223
        ]],[[
            'field'=>'listorder',
            'sort'=>'asc'
        ]]);
        $areas = $areas->keyBy('id');
        $return=[];
        foreach ($categories as $category){
            $query = PositionSupervise::query()->join('point_position', function ($join) {
                $join->on('position_supervise.position_id', '=', 'point_position.id')
                    ->whereNull('point_position.deleted_at');
            })->select(DB::raw('sum(final_score)/sum(full_score) as score_ratio'),'district_id')
                ->where('category_id',$category->id)
                ->groupBy('district_id')->orderBy('score_ratio','desc');
            if($start_time&&$end_time){
                $query->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
            }
            $query_result=$query->get();
            if($query_result->count()){
                foreach ($query_result as $key=>&$res){
                    $res->name = $areas[$res->district_id]->name;
                    $arr = $res;
                    $arr->category_id = $category->id;
                    $arr->category_name = $category->name;
                    $arr->rank = $key+1;
                    $return[]=$arr;
                }
                //$category->area = $query_result;
            }
        }
        return $this->response->success($return);
    }

    public function category(){
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $categories = PointPositionCategoryRepository::getInstance()->all(['id','name','full_score'],[],[[
            'field'=>'id',
            'sort'=>'asc'
        ]]);
        $query = PositionSupervise::query()->select(DB::raw('count(position_id) as count_nums'),'category_id')->join('point_position', function ($join) {
            $join->on('position_supervise.position_id', '=', 'point_position.id');
        });
        $query_wait_reply = clone $query;
        $query_wait_reply->where('position_supervise.status',PositionSupervise::STATUS_WAITING);
        $query_done_reply = clone $query;
        $query_done_reply->where('position_supervise.status','>=',PositionSupervise::STATUS_REPLY);

        if($start_time&&$end_time){
            $query->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
            $query_wait_reply->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
            $query_done_reply->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
        }
        $result_all_nums =$query->groupBy('category_id')->get()->keyBy('category_id');
        $all_nums=collect($result_all_nums)->sum('count_nums');
        $result_wait_reply_nums = $query_wait_reply->groupBy('category_id')->get()->keyBy('category_id')->toArray();
        $result_done_reply_nums = $query_done_reply->groupBy('category_id')->get()->keyBy('category_id')->toArray();

        foreach ($categories as &$item){
            $query_score = PositionSupervise::query()->join('point_position', function ($join) {
                $join->on('position_supervise.position_id', '=', 'point_position.id');
            })->select(DB::raw('sum(allscore)/sum(full_score) as score_ratio'));
            if($start_time&&$end_time){
               $query_score->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
            }
            $item->wait_reply_nums = isset($result_wait_reply_nums[$item->id])?$result_wait_reply_nums[$item->id]['count_nums']:0;
            $item->done_reply_nums = isset($result_done_reply_nums[$item->id])?$result_done_reply_nums[$item->id]['count_nums']:0;
            $item->all_nums = isset($result_all_nums[$item->id])?$result_all_nums[$item->id]['count_nums']:0;
            $base_nums = $item->wait_reply_nums+$item->done_reply_nums;
            $reply_ratio =$base_nums?sprintf('%.2f',$item->done_reply_nums/$base_nums)*100:0;
            $item->reply_ratio = $reply_ratio.'%';
            $count_ratio = $all_nums?sprintf('%.2f',$item->all_nums/$all_nums)*100:0;
            $item->count_ratio = $count_ratio.'%';
            $score_ratio_result = $query_score->where('category_id',$item->id)->first();
            $score_ratio = $score_ratio_result?sprintf('%.2f',$score_ratio_result->score_ratio*100):0;
            $item->score_ratio = $score_ratio.'%';
        }
        return $this->response->success($categories);
    }

    public function user(){
        $rows = $this->request->input('rows',10);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $search=[
            [
                'field' => 'is_supervisor',
                'value' => 1
            ]
        ];
        $orderBy=[[
            'field' => 'id',
            'sort' => 'asc'
        ]];
        $columns=['id','mobile'];
        $return = UserRepository::getInstance()->getList($columns,$search,['limit'=>$rows],$orderBy);
        if($return['result']){
            foreach ($return['result'] as $key=>&$item){
                $item['index_num'] = ($return['page']['current_page']-1)*$rows+$key+1;
                $item['user'] = UserRepository::getInstance()->getBaseInfo($item['id']);
                $query_all = PositionSupervise::query()->where('user_id',$item['id']);
                $query_done = PositionSupervise::query()->where('user_id',$item['id'])->where('status',PositionSupervise::STATUS_DONE);
                if($start_time&&$end_time){
                    $query_all->where('created_at','>=',$start_time)->where('created_at','<=',$end_time);
                    $query_done->where('created_at','>=',$start_time)->where('created_at','<=',$end_time);
                }
                $item['all_nums'] = $query_all->count('id');
                $item['done_nums'] = $query_done->count('id');
            }
        }
        return $this->response->success($return);
    }

    public function position(){
        $rows = $this->request->input('rows',10);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $search=[];
        $orderBy= [
            [
                'field' => 'id',
                'sort' => 'desc'
            ]
        ];
        $columns=[
            'point_position.*'
        ];

        $datas = PointPositionRepository::getInstance()->getList($columns,$search,['limit'=>$rows],$orderBy,'',['category','district','mainunit']);
        if($datas['result']){
            $category_score=[];
            foreach ($datas['result'] as $key=>&$item){
                $item['index_num'] = ($datas['page']['current_page']-1)*$rows+$key+1;
                if(isset($category_score[$item['category_id']])){
                    $item['paper_all_score'] = $category_score[$item['category_id']];
                }else{
                    $all_score = PositionPaper::query()->where('category_id',$item['category_id'])->sum('score');
                    $category_score[$item['category_id']]=$all_score;
                    $item['paper_all_score'] = $all_score;
                }
                $query_all = PositionSupervise::query()->where('position_id',$item['id']);
                $query_score = PositionSupervise::query()->where('position_id',$item['id']);
                $query_below = PositionSuperviseSheet::query()->join('position_supervise',function($join) use($item){
                    $join->on('position_supervise_sheet.supervise_id','=','position_supervise.id')
                        ->where('position_id',$item['id'])
                        ->whereNull('position_supervise.deleted_at');
                })->where('score',0);
                if($start_time&&$end_time){
                    $query_all->where('created_at','>=',$start_time)->where('created_at','<=',$end_time);
                    $query_score->where('created_at','>=',$start_time)->where('created_at','<=',$end_time);
                    $query_below->where('position_supervise_sheet.created_at','>=',$start_time)->where('position_supervise_sheet.created_at','<=',$end_time);
                }
                $item['all_nums'] = $query_all->count('id');
                $item['below_nums'] = $query_below->count(DB::raw('DISTINCT(supervise_id)'));
                $item['qualified_nums'] = $item['all_nums']-$item['below_nums'];
                $item['all_score'] = $query_score->sum('allscore');
                $item['all_final_score'] = $query_score->sum('final_score');
                $item['avg_score'] = $item['all_nums']?sprintf('%.2f',$item['all_final_score']/$item['all_nums']):0;

            }
        }
        return $this->response->success($datas);
    }

    public function positionExport(){
        $admin = $this->request->getAttribute('admin');
        $params = $this->request->all();
        if($admin->district_id){
            $params['district_id'] = $admin->district_id;
        }
        $hash_id = $params['hash_id'];
        $exists = ExportFile::query()->where('hash_id',$hash_id)->first();
        if(!$exists){
            $newname = Utils::createFileName().'.csv';
            $dir = '/uploads/export/'.date('Ym').'/'.date('d').'/';
            $folder = BASE_PATH.'/public'.$dir;
            if (!is_dir($folder)) {
                mkdir($folder, 0777, true);
            }
            $model = new ExportFile();
            $model->hash_id=$hash_id;
            $model->file_url = $dir.$newname;
            $model->type = 'thread';
            $model->params = json_encode($params);
            if(!$model->save()){
                return $this->response->error(10050,'创建导出失败');
            }
            $params['file_url'] = $dir.$newname;
            cache()->set('export_'.$hash_id,0);
            $this->queue_service->push(new PositionStaticJob($params));
            return $this->response->success([
                'hash_id' => $hash_id,
                'progress' => 0,
                'file_url' => config('common.image_prefix').$dir.$newname
            ]);
        }
        return $this->response->success([
            'hash_id' => $hash_id,
            'progress' => cache()->get('export_'.$hash_id),
            'file_url' => config('common.image_prefix').$exists->file_url
        ]);
    }

    public function community(){
        $rows = $this->request->input('rows',10);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $search=[
            [
                'field'=>'category_id',
                'value' => 45
            ]
        ];
        $orderBy= [
            [
                'field' => 'ratio',
                'sort' => 'desc'
            ]
        ];
        $columns=[
            'point_position.name',
            'position_id',
            'point_position_category.full_score',
            DB::raw('count(cc_position_supervise.id) as count_num'),
            DB::raw('sum(cc_position_supervise.allscore) as sum_score'),
            DB::raw('sum(cc_position_supervise.allscore)/count(cc_position_supervise.id) as avg_ratio'),
            DB::raw('sum(cc_position_supervise.allscore)/count(cc_position_supervise.id)/cc_point_position_category.full_score as ratio')
        ];
        $datas = PointPosition::query()->join('position_supervise',function($join){
            $join->on('position_supervise.position_id','=','point_position.id')
                ->whereNull('position_supervise.deleted_at');
        })->join('point_position_category',function($join){
            $join->on('point_position.category_id','=','point_position_category.id')
                ->whereNull('point_position_category.deleted_at');
        })->where('category_id','45')->select($columns)->groupBy('position_id')
            ->orderBy('ratio','desc')
            ->get();
        foreach ($datas as &$data){
            $data->avg_ratio = (int)$data->avg_ratio;
            $data->ratio = sprintf('%.2f',$data->ratio*100).'%';
        }

        return $this->response->success($datas);
    }

    public function categoryPosition(){
        $rows = $this->request->input('rows',10);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $categories = PointPositionCategoryRepository::getInstance()->all(['id','name','full_score'],[],[[
            'field'=>'id',
            'sort'=>'asc'
        ]]);
        $categories = $categories->keyBy('id');
        $areas = AreaRepository::getInstance()->all(['id','name'],[[
            'field'=>'parent_id',
            'value' => 223
        ]],[[
            'field'=>'listorder',
            'sort'=>'asc'
        ]]);
        $return = [];
        foreach ($areas as $area){
            foreach ($categories as $category){
                $query = PositionSupervise::query()->join('point_position', function ($join) {
                    $join->on('position_supervise.position_id', '=', 'point_position.id')
                    ->whereNull('point_position.deleted_at');
                })->select('position_id','category_id','name',
                        DB::raw('count(position_id) as count_nums'),
                        DB::raw('sum(final_score)/count(position_id) as avg_score'),
                        DB::raw('sum(final_score)/sum(full_score) as score_ratio'),
                        'district_id'
                )
                    ->where('category_id',$category->id)
                    ->where('district_id',$area->id)
                    ->groupBy('position_id')->orderBy('score_ratio','desc');
                if($start_time&&$end_time){
                    $query->where('position_supervise.created_at','>=',$start_time)->where('position_supervise.created_at','<=',$end_time);
                }
                $query_result = $query->get();
                if($query_result->count()){
                    foreach ($query_result as $key=>&$res){
                        $arr = $res;
                        $arr->avg_score = sprintf('%.2f',$arr->avg_score);
                        $arr->area_id = $area->id;
                        $arr->area_name = $area->name;
                        $arr->category_id = $category->id;
                        $arr->category_name = $category->name;
                        $arr->rank = $key+1;
                        $arr->full_score = $category->full_score;
                        $return[]=$arr;
                    }
                }
            }
        }
        return $this->response->success($return);
    }

    public function categoryQuestion(){
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $category_id = $this->request->input('category_id',0);
        $category = PointPositionCategoryRepository::getInstance()->one(['id','name'],[[
            'field' => 'id',
            'value' => $category_id
        ]]);
        if(!$category){
            return $this->response->error(100404,'数据不存在');
        }
        $questions = PositionPaperRepository::getInstance()->all(['id','question','score'],[[
            'field' => 'category_id',
            'value' => $category_id
        ]],[[
            'field' => 'listorder',
            'sort' => 'asc'
        ]]);
        $districts = AreaRepository::getInstance()->all(['id','name'],[[
            'field' => 'parent_id',
            'value' => 223
        ]],[[
            'field' => 'listorder',
            'sort' => 'asc'
        ]]);
        foreach ($questions as &$question){
            $query_all = PositionSuperviseSheet::query()->select(
                DB::raw('count('.env('DB_PREFIX').'position_supervise_sheet.id) as count_nums'),
                DB::raw('count('.env('DB_PREFIX').'position_supervise_sheet.score) as all_score'),
                'district_id'
            )->where('paper_id',$question->id)->join('position_supervise',function($join){
                $join->on('position_supervise_sheet.supervise_id','=','position_supervise.id')
                ->whereNull('position_supervise.deleted_at');
            })->join('point_position',function($join){
                $join->on('position_supervise.position_id','=','point_position.id')->whereNull('point_position.deleted_at');
            })->groupBy('district_id');
//            $query_wait = PositionSuperviseSheet::query()->select(
//                DB::raw('count('.env('DB_PREFIX').'position_supervise_sheet.id) as count_nums'),
//                'district_id'
//            )->where('paper_id',$question->id)->join('position_supervise',function($join){
//                $join->on('position_supervise_sheet.supervise_id','=','position_supervise.id');
//            })->join('point_position',function($join){
//                $join->on('position_supervise.position_id','=','point_position.id');
//            })->where('sheet_status','!=',PositionSuperviseSheet::SHEET_STATUS_REPLY)->where('score',0)
//            ->groupBy('district_id');
            if($start_time&&$end_time){
                $query_all->where('position_supervise_sheet.created_at','>=',$start_time)->where('position_supervise_sheet.created_at','<=',$end_time);
                //$query_wait->where('position_supervise_sheet.created_at','>=',$start_time)->where('position_supervise_sheet.created_at','<=',$end_time);
            }
            $all_num_district = $query_all->get()->keyBy('district_id');
            //$wait_num_district = $query_wait->get()->keyBy('district_id');
//            $question['num']=[
//                'all'=> $query_all->get()->keyBy('district_id'),
//                //'wait'=> $query_wait->get()->keyBy('district_id'),
//                'reply'=> $query_done->get()->keyBy('district_id')
//            ];
            foreach ($districts as $district){
                $key = 'district_'.$district->id;
                $all_num=isset($all_num_district[$district->id])?$all_num_district[$district->id]['count_nums']:0;
                $all_score = isset($all_num_district[$district->id])&&$all_num_district[$district->id]['all_score']?$all_num_district[$district->id]['all_score']:0;
                $full_score = $all_num*$question->score;
                $score_ratio = $full_score?$all_score/$full_score:0;
                $score_ratio_show = sprintf('%.2f',$score_ratio*100).'%';
                $question->$key=[
                    'all_num' => $all_num,
                    'score_ratio' => $score_ratio,
                    'score_ratio_show' => $score_ratio_show
                ];
            }
//            foreach ($districts as $district){
//                $key='district_'.$district->id;
//                $query_all = PositionSuperviseSheet::query()->where('paper_id',$question->id)->join('position_supervise',function($join){
//                    $join->on('position_supervise_sheet.supervise_id','=','position_supervise.id');
//                })->join('point_position',function($join) use($district){
//                    $join->on('position_supervise.position_id','=','point_position.id')->where('district_id',$district->id);
//                });
//                $query_wait = PositionSuperviseSheet::query()->where('paper_id',$question->id)->join('position_supervise',function($join){
//                    $join->on('position_supervise_sheet.supervise_id','=','position_supervise.id');
//                })->join('point_position',function($join) use($district){
//                    $join->on('position_supervise.position_id','=','point_position.id')->where('district_id',$district->id);
//                })->where('sheet_status','!=',PositionSuperviseSheet::SHEET_STATUS_REPLY);
//                $query_done = PositionSuperviseSheet::query()->where('paper_id',$question->id)->join('position_supervise',function($join){
//                    $join->on('position_supervise_sheet.supervise_id','=','position_supervise.id');
//                })->join('point_position',function($join) use($district){
//                    $join->on('position_supervise.position_id','=','point_position.id')->where('district_id',$district->id);
//                })->where('sheet_status',PositionSuperviseSheet::SHEET_STATUS_REPLY);
//
//                if($start_time&&$end_time){
//                    $query_all->where('position_supervise_sheet.created_at','>=',$start_time)->where('position_supervise_sheet.created_at','<=',$end_time);
//                    $query_wait->where('position_supervise_sheet.created_at','>=',$start_time)->where('position_supervise_sheet.created_at','<=',$end_time);
//                    $query_done->where('position_supervise_sheet.created_at','>=',$start_time)->where('position_supervise_sheet.created_at','<=',$end_time);
//                }
//                $question->$key=[
//                    'all_num' => $query_all->count('position_supervise_sheet.id'),
//                    'wait_num' => $query_wait->count('position_supervise_sheet.id'),
//                    'reply_num' => $query_done->count('position_supervise_sheet.id'),
//                ];
//            }
        }
        return $this->response->success($questions);
    }

}